package pdb

import (
	"fmt"
	"gitee.com/teval/pts"
	"regexp"
	"strings"
)

type DBType int

const (
	Mssql DBType = iota
	SqlServer
	Oracle
	Mysql
)

type FieldType int

const (
	String FieldType = iota
	Number
	Date
)

var (
	regMatch     = regexp.MustCompile(`[*?]`)
	regOpera     = regexp.MustCompile(`^([><]=?|!?=)`)
	regRange     = regexp.MustCompile(`^\d+(\.\d*)?:\d+(\.\d*)?$`)
	regNum       = regexp.MustCompile(`^\d+(\.\d*)?$`)
	regDateRule  = `\d{2,4}(?:[-/]?(?:0[1-9]|1[0-2])(?:[-/]?(?:0[1-9]|[1-2][0-9]|3[0-1])(?: (?:[0-5][0-9])(?::?(?:[0-5][0-9])(?::?(?:[0-5][0-9])(?:\.\d*)?)?)?)?)?)?`
	regDate      = regexp.MustCompile(regDateRule)
	regDateMust  = regexp.MustCompile(fmt.Sprintf(`^%s$`, regDateRule))
	regDateRange = regexp.MustCompile(fmt.Sprintf(`^%s:%s$`, regDateRule, regDateRule))
)

type WhereParam struct {
	Field     string                    // 字段名称
	Type      FieldType                 // 字段类型
	Value     string                    // 查询的条件值
	Format    string                    // format：date类型，time.Parse(value, format)；其它，fmt.Sprintf(format, value)
	Formatter func(value string) string // 自定义格式化方法（优先于format）
}

// ParseWhere 将 params 中的查询参数，转换为适用 gorm 的 where 条件
//
//	参数
//	 - dbType：数据库类型：oracle、mssql、sqlserver、mysql
//	 - params：查询参数 WhereParam
//
//	返回值
//	 - where：带 ? 占位符的字符串，可用于 gorm.DB.Where(where, values...)
func ParseWhere(dbType DBType, params []WhereParam) (where string, values []any) {
	var wheres []string

	for _, param := range params {
		switch param.Type {
		case Number:
			if value := strings.TrimRight(pts.Str(param.Value), " \t\r\n"); value != "" {
				var ws []string
				for _, val := range strings.Split(value, "|") {
					if regOpera.MatchString(val) {
						var opera string
						val = regOpera.ReplaceAllStringFunc(val, func(s string) string {
							opera = s
							return ""
						})

						if regNum.MatchString(val) {
							ws = append(ws, fmt.Sprintf(`%s %s ?`, param.Field, opera))
							values = append(values, val)
						} else if pts.ArrIncludes([]string{"=", "!="}, opera) && val == "" {
							ws = append(ws, fmt.Sprintf(`%s(%s, '') %s ''`,
								pts.Decode(pts.Str(dbType),
									pts.Str(Oracle), "NVL",
									pts.Str(Mysql), "IFNULL",
									pts.Str(Mssql), "ISNULL",
									pts.Str(SqlServer), "ISNULL",
								),
								param.Field,
								opera))
						} else {
							ws = append(ws, `1 = 2`)
						}

					} else if regRange.MatchString(val) {
						if arr := strings.Split(val, ":"); len(arr) > 1 {
							ws = append(ws, fmt.Sprintf(`%s BETWEEN ? AND ?`, param.Field))
							values = append(values, arr[0], arr[1])
						} else {
							ws = append(ws, `1 = 2`)
						}
					} else if regNum.MatchString(val) {
						ws = append(ws, fmt.Sprintf(` %s = ? `, param.Field))
						values = append(values, val)
					} else {
						ws = append(ws, `1 = 2`)
					}
				}

				if len(ws) > 0 {
					wheres = append(wheres, fmt.Sprintf(`( %s )`, strings.Join(ws, " ) OR ( ")))
				}
			}
		case Date:
			if value := strings.TrimRight(pts.Str(param.Value), " \t\r\n"); value != "" {
				var ws []string
				for _, val := range strings.Split(value, "|") {
					if regOpera.MatchString(val) {
						var opera string
						val = regOpera.ReplaceAllStringFunc(val, func(s string) string {
							opera = s
							return ""
						})

						if regDateMust.MatchString(val) {
							ws = append(ws, fmt.Sprintf(`%s %s ?`, param.Field, opera))
							values = append(values, val)
						} else if pts.ArrIncludes([]string{"=", "!="}, opera) && val == "" {
							ws = append(ws, fmt.Sprintf(`%s(%s, '') %s ''`,
								pts.Decode(pts.Str(dbType),
									pts.Str(Oracle), "NVL",
									pts.Str(Mysql), "IFNULL",
									pts.Str(Mssql), "ISNULL",
									pts.Str(SqlServer), "ISNULL",
								),
								param.Field,
								opera))
						} else {
							ws = append(ws, `1 = 2`)
						}
					} else if regDateRange.MatchString(val) {
						if arr := regDate.FindAllStringSubmatch(val, 2); len(arr) > 1 && len(arr[0]) > 0 && len(arr[1]) > 0 {
							ws = append(ws, fmt.Sprintf(`%s BETWEEN ? AND ?`, param.Field))
							values = append(values, arr[0][0], arr[1][0])
						} else {
							ws = append(ws, `1 = 2`)
						}
					} else if regDateMust.MatchString(val) {
						ws = append(ws, fmt.Sprintf(` %s = ? `, param.Field))
						values = append(values, val)
					} else {
						ws = append(ws, `1 = 2`)
					}
				}

				if len(ws) > 0 {
					wheres = append(wheres, fmt.Sprintf(`( %s )`, strings.Join(ws, " ) OR ( ")))
				}
			}
		// case String:
		default: // 默认当 String 处理
			if value := strings.TrimRight(pts.Str(param.Value), " \t\r\n"); value != "" {
				var ws []string
				for _, val := range strings.Split(value, "|") {
					if regMatch.MatchString(val) {
						val = strings.ReplaceAll(val, "*", "%")
						val = strings.ReplaceAll(val, "?", "_")

						ws = append(ws, fmt.Sprintf(`%s LIKE ?`, param.Field))
					} else if regOpera.MatchString(val) {
						var opera string

						val = regOpera.ReplaceAllStringFunc(val, func(s string) string {
							opera = s
							return ""
						})

						ws = append(ws, fmt.Sprintf(`%s(%s, '') %s ?`,
							pts.Decode(pts.Str(dbType),
								pts.Str(Oracle), "NVL",
								pts.Str(Mysql), "IFNULL",
								pts.Str(Mssql), "ISNULL",
								pts.Str(SqlServer), "ISNULL",
							),
							param.Field,
							opera,
						))
					} else {
						ws = append(ws, fmt.Sprintf(`%s(%s, '') = ?`,
							pts.Decode(pts.Str(dbType),
								pts.Str(Oracle), "NVL",
								pts.Str(Mysql), "IFNULL",
								pts.Str(Mssql), "ISNULL",
								pts.Str(SqlServer), "ISNULL",
							),
							param.Field,
						))
					}
					values = append(values, val)
				}

				if len(ws) > 0 {
					wheres = append(wheres, fmt.Sprintf(`( %s )`, strings.Join(ws, " ) OR ( ")))
				}
			}
		}
	}

	return fmt.Sprintf(`( %s )`, pts.Nvl(strings.Join(wheres, " ) AND ( "), "1 = 1")), values
}
